package com.mairuide._frame.utils.sheethandler;

import java.util.ArrayList;
import java.util.LinkedList;
import java.util.List;
import java.util.Map;

import org.apache.poi.xssf.binary.XSSFBSheetHandler.SheetContentsHandler;
import org.apache.poi.xssf.usermodel.XSSFComment;
import org.springframework.jdbc.core.JdbcTemplate;

import com.mairuide._frame.utils.RegUtil;
import com.mairuide._frame.utils.SpringUtil;
import com.mairuide._frame.utils.StringUtils;
import com.mairuide.bill.DealFlowService;

public class ExcelEventParser implements SheetContentsHandler{ 
	//行结果
    private LinkedList<String> row ;  
    //结果集
    private LinkedList<List<String>> result = new LinkedList<List<String>>();
    //参数
    private Map<String , Object> paramMap;
    //临时表字列长度
    private List<Integer> tempRowsNumber = new ArrayList<Integer>();
    @Override  
    public void startRow(int rowNum) {
    	row = new LinkedList<String>();
    }  
  
    @Override  
    public void endRow(int rowNum) {
    	result.add(row);
    	if(rowNum <= 10) {
    		tempRowsNumber.add(row.size());
    	}
    	if (result != null && result.size()>=10000){
            doSomething(paramMap.get("uid").toString());
            result = new LinkedList<>();
        }
    }  
  
    @Override  
    public void cell(String cellReference, String formattedValue, XSSFComment comment) {
		row.add(formattedValue); 
    }  
  
    @Override  
    public void headerFooter(String text, boolean isHeader, String tagName) {  
    }

	@Override
	public void hyperlinkCell(String cellReference, String formattedValue, String url, String toolTip,
			XSSFComment comment) {
	}
	public void doSomething(String uid){
		JdbcTemplate jdbcTemplate = SpringUtil.getBean(JdbcTemplate.class);
		DealFlowService dealFlowService = SpringUtil.getBean(DealFlowService.class);
		//创建临时表
        dealFlowService.createTempTable(uid,tempRowsNumber);
        StringBuffer sbfStart = new StringBuffer("INSERT INTO TEMP_"+uid+" (");
        for (int i = 0; i < result.getLast().size()+1; i++) {
        	sbfStart.append("temp_"+i+",");
        }
        sbfStart.deleteCharAt(sbfStart.length() - 1);
        sbfStart.append(") ");
        for (int i = 0; i < result.size(); i++) {
        	StringBuffer sbfEnd = new StringBuffer(sbfStart);
        	for(int j = 0; j < result.get(i).size(); j ++) {
        		if(j == 0) {
        			sbfEnd.append(" values('"+i+"',");
        		}
        		String val = result.get(i).get(j);
        		if (StringUtils.isNotEmpty(val)) {
                    val = RegUtil.replaceSingleQuotes(val);
                    sbfEnd.append("'"+val+"',");
                }else {
                	sbfEnd.append("null,");
                }
        		
        	}
        	sbfEnd.deleteCharAt(sbfEnd.length() - 1);
        	sbfEnd.append(")");
            jdbcTemplate.execute(sbfEnd.toString());
        }
    }
	
	public LinkedList<List<String>> getResult() {
		return result;
	}

	public void setResult(LinkedList<List<String>> result) {
		this.result = result;
	}

	public Map<String, Object> getParamMap() {
		return paramMap;
	}

	public void setParamMap(Map<String, Object> paramMap) {
		this.paramMap = paramMap;
	}
	
}
